package com.ruoyi.StarStudio.Mapper;

import com.ruoyi.StarStudio.Domain.Annualoutput;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
@SuppressWarnings("all")
public interface AnnualOutputMapper {

    //根据地块id查询年产量信息
    @Select("SELECT  *  FROM (SELECT op.*, zop.zone_id FROM annualoutput op, zone_annualoutput zop WHERE op.annualoutput_id = zop.annualoutput_id AND zop.zone_id = #{zone_id}) a ORDER BY year DESC")
    public List<Annualoutput> getByZoneId(Integer zone_id);

    //根据年产量id查询年产量信息
    @Select("SELECT  *  FROM (SELECT op.*, zop.zone_id FROM annualoutput op, zone_annualoutput zop WHERE op.annualoutput_id = zop.annualoutput_id) a WHERE a.annualoutput_id = #{annualoutput_Id}")
    public Annualoutput getByOutputId(Integer annualoutput_Id);

    //增加年产量信息
    @Insert("insert into annualoutput values(null, #{year}, #{annualOutput})")
    @Options(useGeneratedKeys = true, keyProperty = "annualoutput_Id", keyColumn = "annualoutput_id")  //设置主键自增，指定主键对应的实体类属性名和数据库字段名
    public int add(Annualoutput annualoutput);

    //增加年产量对应地块信息至中间表
    @Insert("insert into zone_annualoutput values(#{zone_id}, #{annualoutput_Id})")
    public void addToZone_AnnualOutput(@Param("zone_id") Integer zone_id,@Param("annualoutput_Id") Integer annualoutput_Id);

    //修改年产量信息
    @Update("update annualoutput set year=#{year},annualoutput=#{annualOutput} where annualoutput_id = #{annualoutput_Id}")
    public void update(Annualoutput annualoutput);

    //根据年产量id删除地块-年产量中间表项
    @Delete("delete from zone_annualoutput where annualoutput_id = #{annualoutput_Id}")
    public void deleteFromZone_AnnualOutput(Integer annualoutput_Id);

    //根据年产量id删除年产量信息
    @Delete("delete from annualoutput where annualoutput_id = #{annualoutput_Id}")
    public void delete(Integer annualoutput_Id);

    //根据地块id分页查询年产量信息
    @Select("SELECT * FROM (SELECT op.*, zop.zone_id FROM annualoutput op, zone_annualoutput zop WHERE op.annualoutput_id = zop.annualoutput_id AND zop.zone_id = #{zone_id}) a ORDER BY year DESC")
    List<Annualoutput> getPageByZoneId(Integer zone_id);

    List<Annualoutput> getList(Annualoutput annualoutput);  //带搜索查询

    @Select("select aop.* from annualoutput aop, zone_annualoutput zaop where zaop.zone_id = #{zone_id} and aop.year = #{year} limit 1")
    public Annualoutput getByZoneAndYear(Annualoutput annualoutput);
}
